Copyright: Copyright © 1996 Microsoft Corp.
Citation: Copyright © 1996 Microsoft Corp.


Introduction

The OLE DB SDK contains an OLE DB provider for ODBC data. This document describes the extent of implementation of the ODBC Provider, explains the procedures for installing, configuring, and testing the provider, and lists the limitations of the current implementation.

OLE DB Specification Compliance

The ODBC Provider is compliant with version 1.1 of the OLE DB Specification, which is included in the OLE DB SDK.


Installation Notes

The Setup program, included on the OLE DB SDK compact disc, installs the following files to the hard disk of your workstation or server.

\Program Files\Common Files\System\OLE DB or
<Install Dir>\REDIST

File Name	Description	Required By
MSDADC.DLL	OLE DB data conversion object	ODBC provider
MSDAENUM.DLL	OLE DB enumerator object
MSDAER.DLL	OLE DB error object	ODBC provider
MSDAERR.DLL	Resource file for OLE DB error object	OLE DB error object
MSDASQL.DLL	ODBC provider
MSDASQLR.DLL	Resource file for ODBC provider	ODBC provider
MSDATL.DLL	Memory/Array manager	Temp table service provider, error object, ODBC provider
MSDATT.DLL	Temporary table service provider	ODBC provider
<Install Dir>\DOC

File Name	Description	Required By
MSDASQL.HLP	Help file for the ODBC provider
MSDASQL.CNT	Help contents file for the ODBC provider Help	MSDASQL.HLP
<Install Dir>\INCLUDE

File Name	Description	Required By
MSDASQL.H	Provider-specific defines	ODBC provider
MSDADC.H	Data conversion defines	ODBC provider


Predefined ODBC Data Sources

As part of the ODBC Provider installation the following 32-bit ODBC data sources are created:

•	OLE_DB_NWind_Jet. This data source points to the NorthWind Access database shipped with the OLE DB SDK. This is \OLEDBSDK\SMPLDATA\ACCESS\NWIND.MDB.

•	OLE_DB_NWind_SQL. This data source points to the Microsoft® SQL Server™ “pubs” database. The server is set to “.” and the database name is set to “pubs”. You may need to modify this data source to use it on your system.


Supported Interfaces

The ODBC Provider supports the following OLE DB interfaces. For implementation details and known limitations, see the “Interface Implementation Notes” and “Known Limitations” sections in this Help file.

IAccessor
IColumnsInfo
IColumnsRowset
ICommand
ICommandPrepare
ICommandProperties
ICommandText
ICommandWithParameters
IConnectionPoint
IConnectionPointContainer
IConvertType
IDBCreateCommand
IDBCreateSession
IDBInfo
IDBInitialize
IDBProperties
IDBSchemaRowset
IErrorLookup
IGetDataSource
IMultipleResults
IOpenRowset
IPersistFile (on data source object only)
IRowset
IRowsetChange
IRowsetIdentity
IRowsetInfo
IRowsetLocate
IRowsetResynch
IRowsetUpdate
ISessionProperties
ISourcesRowset
ISupportErrorInfo
ITransaction
ITransactionLocal
ITransactionOptions


Obtaining Data Source Objects

The ODBC Provider supports creating an initialized data source object through a file moniker:

•	Create a file moniker (.dsn file). A .dsn file contains connection information.

•	Bind to this file moniker, asking for IID_IDBInitialize. This returns an uninitialized data source object.

A data source object can also be obtained through CoCreateInstance using the CLSID MSDASQL.


Initialization Properties and .dsn Files

Initialization Properties

When the consumer calls IDBInitialize::Initialize, the ODBC Provider calls the ODBC functions SQLSetConnectOption, to set various connection options, and SQLDriverConnect, to connect to an ODBC data source. The values passed to SQLSetConnectOption and SQLDriverConnect come from the initialization properties set by the consumer.

When the data source object is first created, the value of each initialization property is set to VT_EMPTY. If the consumer then calls IPeristFile::Load to load a .dsn file, the ODBC Provider reads the values of the properties stored in the .dsn file and sets initialization properties accordingly. When the consumer calls Initialize, it can pass values for initialization properties; these overwrite the values read from the .dsn file. When the consumer calls IDBInitialize::Uninitialize, the value of each initialization property is set back to VT_EMPTY.

.dsn Files

.dsn files allow the consumer to store the initialization properties used to connect to a particular ODBC data source. The consumer can then reload the values from a .dsn file at a later time and reconnect to the same data source. The format of a .dsn file is the same as that of an .ini file; the ODBC Provider reads and writes information from it with SQLWriteFileDSN and SQLReadFileDSN.

To save properties in a .dsn file, the consumer calls IPersistFile::Save on the data source object. To load properties from a .dsn file, the consumer calls IPersistFile::Load on an uninitialized data source object.

.dsn files can also be used to create a moniker to obtain a data source object. The consumer calls CreateFileMoniker with the .dsn file. This creates a moniker which can then be bound with BindMoniker, thus creating an uninitialized data source object.

Initialization Properties Used

The following table lists the initialization properties used by the ODBC Provider, the corresponding keyword used in the .dsn file, and how they are used. Any properties not listed are not used by the ODBC Provider.

Property	.dsn File Keyword	Description
DBPROP_AUTH_ PASSWORD	AUTH_PASSWORD	Passed as the value of the PWD keyword in SQLDriverConnect.
DBPROP_AUTH_ USERID	AUTH_USERID	Passed as the value of the UID keyword in SQLDriverConnect.
DBPROP_INIT_ DATASOURCE	INIT_DATASOURCE	Passed as the value of the DSN keyword in SQLDriverConnect.
DBPROP_INIT_ HWND	INIT_HWND	Passed as the value of the hwnd argument in SQLDriverConnect.
DBPROP_INIT_ LOCATION	INIT_LOCATION	Passed as the value of the Server keyword in SQLDriverConnect (Microsoft SQL Server driver only).
DBPROP_INIT_ MODE	INIT_MODE	Passed to SQLSetConnectOption as the value of the SQL_ACCESS_MODE connection option. Only DB_MODE_READ and DB_MODE_READWRITE are supported.
DBPROP_INIT_ PROMPT	INIT_PROMPT	Passed as the value of the fDriverCompletion parameter in SQLDriverConnect.
DBPROP_INIT_ PROVIDERSTRING	INIT_ PROVIDERSTRING	Complete ODBC connection string (identical to the szConnStr argument in SQLDriverConnect.
DBPROP_INIT_ TIMEOUT	INIT_TIMEOUT	Passed to SQLSetConnectOption as the value of the SQL_LOGIN_TIMEOUT connection option.


ODBC Provider Properties

The following property sets are specific to the ODBC Provider.

The DBPROPSET_PROVIDERDATASOURCEINFO Property Set

The DBPROPSET_PROVIDERDATASOURCEINFO property set contains the following properties.

Property Name	Meaning
KAGPROP_ACCESSIBLEPROCEDURES	Accessible procedures
KAGPROP_ACCESSIBLETABLES	Accessible tables
KAGPROP_ ACTIVESESSIONS	Active sessions
KAGPROP_OJCAPABILITY	Outer join capabilities
KAGPROP_PROCEDURES	Stored procedures
KAGPROP_DRIVERNAME	Driver name
KAGPROP_DRIVERVER	Driver version
KAGPROP_DRIVERODBCVER	Driver ODBC version
KAGPROP_FILEUSAGE	File usage
KAGPROP_LIKEESCAPECLAUSE	Like escape clause
KAGPROP_SPECIALCHARACTERS	Special characters
KAGPROP_MAXCOLUMNSINGROUPBY	Maximum columns in Group By clause
KAGPROP_MAXCOLUMNSININDEX	Maximum columns in index
KAGPROP_MAXCOLUMNSINORDERBY	Maximum columns in Order By clause
KAGPROP_MAXCOLUMNSINSELECT	Maximum columns in Select statement
KAGPROP_MAXCOLUMNSINTABLE	Maximum columns in table
KAGPROP_NUMERICFUNCTIONS	Numeric functions
KAGPROP_OUTERJOINS	Outer joins
KAGPROP_STRINGFUNCTIONS	String functions
KAGPROP_SYSTEMFUNCTIONS	System functions
KAGPROP_TIMEDATEFUNCTIONS	Time and date functions
KAGPROP_ODBCSQLOPTIEF	Indicates whether the data source supports the optional Integrity Enhancement Facility
KAGPROP_ODBCSQLCONFORMANCE	SQL grammar supported by the ODBC driver
The DBPROPSET_PROVIDERROWSET Property Set

The DBPROPSET_PROVIDERROWSET property set contains the following properties.

Property Name	Meaning
KAGPROP_QUERYBASEDUPDATES	Query-based updates, deletes, and inserts
KAGPROP_CONCURRENCY	Mask of ODBC concurrency to support
KAGPROP_BLOBSONFOCURSOR	BLOB accessibility on forward-only cursors
KAGPROP_INCLUDENONEXACT	Include SQL_FLOAT, SQL_DOUBLE, and SQL_REAL in QBU WHERE clauses
KAGPROP_MARSHALLABLE	Generate a rowset that can be marshalled
KAGPROP_POSITIONONNEWROW	Position on the last row after the insert operation completes
KAGPROP_IROWSETCHANGEEXTINFO	IRowsetChange interface extended information support
KAGPROP_CURSOR	ODBC cursor type


Supported Platforms

The ODBC Provider is supported on Windows NT® version 4.0, on Intel, Alpha, and PowerPC platforms. The ODBC Provider is also supported on the Windows® 95 platform for x86 machines.


General Implementation Notes

The following notes discuss how the ODBC Provider is implemented.

•	When the consumer calls IUnknown::Release for a session and the reference count of the session drops to zero (that is, when the session is actually released), any outstanding transactions are aborted.

•	To access Microsoft SQL Server data through the ODBC Provider, the consumer should use the version of the Microsoft SQL Server ODBC Driver shipped in the OLE DB SDK.

•	All support objects (conversion object, temporary table service object, and error collection object) must be registered for the ODBC Provider to be instantiated. In addition, the memory and array manager must be in the ODBC Provider directory or in the path.

•	Although the ODBC Provider advertises support for both threading models, only the free-threaded model is supported.

•	Although the OLE DB Programmer’s Reference specifies that methods capable of returning DB_S_ERRORSOCCURRED will do so only when one or more errors occur, the ODBC Provider returns DB_S_ERRORSOCCURRED when a warning or an error occurs in one or more items. The OLE DB Programmer’s Reference specifies that if only warnings occur, providers should return S_OK.

•	The ODBC Provider does not support the binding of output parameters more than once in a single accessor. This is due to the lack of support for binding an output parameter to different addresses in ODBC.

•	If a command times out, one of the following errors is returned:

•	ICommand::Execute returns DB_E_ABORTLIMITREACHED.

•	IOpenRowset::OpenRowset returns E_FAIL.

•	IDBSchemaRowset::GetRowset returns E_FAIL.

•	IColumnsRowset::GetColumnsRowset (Not applicable).

•	ISourcesRowset::GetSourcesRowset (Not applicable).


Interface Implementation Notes

The following sections discuss how the ODBC Provider implements some interfaces.

IAccessor

•	Accessors may not always be validated immediately at the time of creation. Instead, they may be validated at the time the first row is fetched. Errors will be returned at the first attempt to use such an accessor.

•	The ODBC Provider supports DBACCESSOR_PASSBYREF accessors. These accessors return a single pointer to the ODBC Provider’s internal row buffer. This provides a significant speed improvement over copy or IMalloc bindings. The pointer is only valid while the row handle is active (until ReleaseRows is called).

•	The OLE DB specification does not enable a consumer to query for the layout of the internal buffer. The ODBC Provider will return DB_E_BADBINDINFO if the type does not match exactly, or if the location of the status or length is incorrect.

•	The DBACCESSOR_OPTIMIZED flag is not supported by the ODBC Provider. The flag is ignored (for example, there is no special processing) if specified on an accessor.

IColumnsInfo

•	Columns that have an ODBC SQL type of SQL_LONGVARCHAR or SQL_LONGVARBINARY are returned as type DBTYPE_BYTES or DBTYPE_STR, and the DBCOLUMNFLAG_ISLONG is set in the dwFlags element of the DBCOLUMNINFO structure. The ODBC Provider does not instantiate an OLE COM object for these columns. Instead these settings signify that the bytes stored in the column can be retrieved or stored by requesting an ISequentialStream interface, through which the bytes may be read from or written to.

•	In some ODBC drivers, duplicate columns in an unprepared command cause the ODBC Provider to receive unexpected column names. If the command contains duplicate columns and the consumer prepares the command, unprepares the command, then executes the command, these drivers do not generate column names for the duplicate columns. This causes a warning, DB_S_ERRORSOCCURRED, in IColumnsInfo::MapColumnsID.

•	The ODBC Provider does not support the following DBCOLUMNFLAGS in IColumnsInfo::GetColumnInfo:

DBCOLUMNFLAGS_MAYDEFER

DBCOLUMNFLAGS_WRITE

DBCOLUMNFLAGS_CACHEDEFERRED

IColumnsRowset

•	NULL values are returned in many columns. These are cases in which the information is not available by means of ODBC. OLE DB requires that the columns be returned and set to NULL because, in most cases, not exposing the column can result in consumers making incorrect assumptions about the values. These columns are always set to NULL:

DBCOLUMN_COLLATINGSEQUENCE

DBCOLUMN_COMPUTEMODE

DBCOLUMN_DEFAULTVALUE

DBCOLUMN_DOMAIN

DBCOLUMN_HASDEFAULT

DBCOLUMN_ISUNIQUE.

•	Driver-specific columns can be requested. There is no way to return the list of what these may be for the driver in use. The values are obtained through SQLColAttributes. Consumers should specify a COLUMNID of type DBCOLKIND_GUID_PROPID, with guid equal to GUID_NULL, and ulPropid equal to value of the ODBC driver-specific option (the fDescType parameter in SQLColAttributes). A heuristic is used to determine the data type of the option from the ODBC driver.

ICommandProperties

•	When used with the Microsoft SQL Server ODBC driver, the ODBC Provider cannot support both DBPROP_CANHOLDROWS and DBPROP_OTHERINSERT both being set to VARIANT_TRUE.

ICommandText

•	If SQLNativeSQL is supported in the underlying ODBC driver, the ODBC Provider will initiate a call to this ODBC function when the consumer calls ICommandText::GetCommandText with pguidDialect set to NULL. The string returned from this call is not cached; users must call ICommandText::SetCommandText with the string returned by the ODBC driver to use the native SQL statement.

•	If pguidDialect is set to NULL on input, the ODBC Provider returns the command in ppwszCommand in the dialect used to set the command text. The ODBC Provider also returns S_OK (barring any other errors) rather than DB_S_DIALECTIGNORED.

ICommandWithParameters

•	The data type returned in ICommandWithParameters::GetParameterInfo should be considered without regard to sign. For example, if a database server is expected to return DBTYPE_I2, the ODBC Provider can return DBTYPE_UI2; this ambiguity occurs because the underlying ODBC function SQLDescribeParam does not return the sign of the data type. In addition, the following flags are not set because the ODBC Provider has no mechanism to obtain the status from the ODBC driver with SQLDescribeParam:

DBPARAMFLAGS_ISSIGNED

DBPARAMFLAGS_ISINPUT

DBPARAMFLAGS_ISOUTPUT

•	If the database server returns SQL_NULLABLE_UNKNOWN the ODBC Provider will return DBPARAMFLAGS_ISNULLABLE.

IDBSchemaRowset

•	The ODBC Provider cannot support restrictions on schema columns if the restrictions are not supported in the underlying ODBC driver.

•	When used with the Microsoft Access driver, IDBSchemaRowset supports the following schema rowsets:
CATALOGS
COLUMNS
INDEXES
PROCEDURES
PROCEDURE_PARAMETERS
PROVIDER_TYPES
TABLES

•	When used with the Microsoft SQL Server driver, IDBSchemaRowset supports the following schema rowsets:
CATALOGS
COLUMNS
FOREIGN_KEYS
INDEXES
PRIMARY_KEYS
PROCEDURES
PROCEDURE_PARAMETERS
PROVIDER_TYPES
TABLES

•	NULL values are returned in many columns which ANSI SQL 92 specifies as NOT NULL. These are cases where the information is not available by means of ODBC.

•	Schema rowsets do not expose IColumnsRowset or IRowsetLocate.

•	The COLUMNS schema rowset is dependent on the information returned in the ODBC driver’s implementation of SQLColumns. Since ODBC does not return ordinal information, the ODBC Provider always sets the value of ORDINAL_POSITION to NULL.

The ODBC Provider returns any driver-specific columns that the ODBC driver returns in SQLColumns. This information is returned in columns beyond those described in the OLE DB specification.

These additional columns include:

ORDINAL, which is returned by the Microsoft Access ODBC driver.

ss_data_type and colid, which are returned by the Microsoft SQL Server 6.0 ODBC driver.

"COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE", and "SS_DATA_TYPE”, which are returned by the Microsoft SQL Server 6.5 ODBC driver.

•	In the INDEXES schema rowset, the value of the TYPE column is set to DBPROPVAL_IT_BTREE if the value of the TYPE column returned by SQLStatistics is SQL_INDEX_CLUSTERED. The value of the NULL_COLLATION column is set to the value returned by SQLGetInfo for the SQL_NULL_COLLATION option.

•	In the PROVIDER_TYPES schema rowset, the value of the DATA_TYPE column is set to DBTYPE_BYTES or DBTYPE_IUNKNOWN if the value of the DATA_TYPE column returned by SQLGetTypeInfo is a driver-specific SQL data type indicator.

•	The PROVIDER_TYPES schema rowset does not support any restrictions.

IRowset

•	GetNextRows may skip over rows if the underlying ODBC driver packs data and some rows of the current rowset have been deleted.

IRowsetChange

•	IRowsetChange is implemented using the ODBC function SQLSetPos. It therefore can be exposed only when the underlying ODBC driver supports SQLSetPos. If the underlying driver does not support SQLSetPos, query-based updating will be used based on an optimistic, by-value scheme.

•	Newly inserted rows can be updated or deleted only through query-based updates; if query-based updating is unavailable, newly-inserted rows cannot be updated.

ITransaction

•	Consumers that use the ODBC Provider should be aware of the ITransaction::Abort and ITransaction::Commit error XACT_E_CANTRETAIN, which specifies that retaining abort and commit operations are not supported and that the specified abort or commit succeeded leaving the session in autocommit mode. After receiving this error, consumers should check for extended error information. If extended error information is available, consumers should call ITransaction::GetTransactionInfo to determine the current transaction mode (manual commit or autocommit); there may be some cases where the ODBC Provider cannot put the session in autocommit mode despite the information implied in the XACT_E_CANTRETAIN error.


Data Types

This section describes data types in the ODBC Provider.

Default Data Type Mappings

The ODBC Provider binds to the ODBC data source using the types in the table below. The SQL type is queried using SQLDescribeCol. The sign of the data type (signed/unsigned) is determined using SQLColAttributes. It is used in deciding which C type to use in internal buffers and which type indicator to return through IColumnsInfo::GetColumnInfo.

SQL Type Indicator	Indicator of C Type Used For Internal Buffers	OLE DB Type Indicator
SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_DECIMAL, SQL_NUMERIC	SQL_C_CHAR	DBTYPE_STR
SQL_BIT	SQL_C_BIT	DBTYPE_BOOL
SQL_TINYINT, SQL_SMALLINT	SQL_C_USHORT, SQL_C_SSHORT	DBTYPE_I2
SQL_INTEGER	SQL_C_ULONG, SQL_C_SLONG	DBTYPE_I4
SQL_BIGINT	SQL_C_STR [1]	DBTYPE_STR
SQL_REAL	SQL_C_FLOAT	DBTYPE_R4
SQL_FLOAT, SQL_DOUBLE	SQL_C_DOUBLE	DBTYPE_R8
SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY	SQL_C_BINARY	DBTYPE_BYTES
SQL_DATE	SQL_C_DATE	DBTYPE_DATE
SQL_TIME	SQL_C_TIME	DBTYPE_DATE
SQL_TIMESTAMP	SQL_C_TIMESTAMP	DBTYPE_DATE

1	This is due to lack of support in the ODBC specification for a SQL_C type for 8-byte integers.

Length Binding for Numerics

For types DBTYPE_UI1, DBTYPE_I2, DBTYPE_I4, DBTYPE_I8, DBTYPE_R4, DBTYPE_R8, DBTYPE_CY, DBTYPE_NUMERIC, the length binding is always set to the fixed size of the destination binding type, rather than the internal source type.

String Truncation

Strings are truncated when the destination length is smaller than the source length.

Support for Long Data Types

Consumer applications can bind to long data columns by binding to IUnknown and querying for the ISequentialStream interface on the data or by binding to _BYTES or _STR (depending on the underlying data type) to get the data in line. If the consumer chooses to use the ISequentialStream approach, support is limited to one BLOB per accessor.

The ODBC Provider supports STGM_READ on IRowset::GetData or IRowset::SetData; STGM_WRITE is not supported for either method. To write long data using IRowset::GetData or IRowset::SetData, follow these steps:

1	Set pObject->dwFlags == STGM_READ;
set pObject->iid==IID_ISequentialStream.
(Only IID_ISequentialStream is supported; IStream, IStorage, and ILockBytes are not.)

2	Bind the storage columns. IRowset::GetData and IRowset::SetData permit only one storage column to be bound at a time.

3	Pass the instantiated ISequentialStream pointer in the pData buffer at the correct offset (specified by obValue).

IRowset::SetData reads the data by calling ISequentialStream::Read on the object until it reaches the end of the stream.

In some situations, calling  rowset methods while an ISequentialStream interface is active and implemented on the rowset may cause one of the following situations:

•	Calls to methods other than AddRefRows or GetData while accessing non-BLOB columns in the single-threaded case return E_UNEXPECTED.

•	Calls to methods other than AddRefRows or GetData while accessing non-BLOB columns in the multi-threaded case on the same thread as the ISequentialStream implementation return E_UNEXPECTED.

•	Calls to ReleaseRows in the single-threaded case will cause the ISequentialStream object to be invalidated and placed in a zombied state.

•	Calls to ReleaseRows in the multi-threaded case on the same thread as the ISequentialStream implementation will cause the ISequentialStream object to be invalidated and placed in a zombied state.

•	Calls to methods AddRefRows or Release will block if called from a thread other than the thread using the ISequentialStream implementation in the multi-threaded case.

To support long data types, the consumer application must meet at least one of the following conditions:

•	The OLE DB IRowsetLocate interface is available.

•	SQLExtendedFetch and SQLSetPos(_POSITION) are available in the underlying ODBC driver and DBPROP_CANHOLDROWS is set to VARIANT_FALSE.

•	The underlying ODBC cursor is forward-only. In this situation, BLOB data is available if there are no non-BLOB columns following a BLOB column or, BLOB data is available regardless of position, if the KAGPROP_BLOBSONFOCURSOR property is set to VARIANT_TRUE.

Long data in a newly-inserted row that contains BLOB data columns cannot be read immediately after the row is inserted if the insertion occurred in immediate update mode or if the BLOB data was provided through an ISequentialStream object. The consumer must use IRowset::GetNextRows, IRowsetLocate::GetRowsAt, or IRowsetLocate::GetRowsByBookmark to reposition on the newly-inserted row and gain access to the long data columns.

Note  Implementations that use query-based updates do not permit BLOB manipulations.

Using the KAGPROP_BLOBSONFOCURSOR Property

The KAGPROP_BLOBSONFOCURSOR property enables users to access BLOBs in a forward-only rowset regardless of the position of the column containing the BLOB in the column list. If this property is set to VARIANT_TRUE, users can access BLOB data in columns other than the last column in forward-only rowsets; if it set to VARIANT_FALSE, users can access BLOB data only if there are no additional BLOB columns following a BLOB column in the forward-only rowset. If any non-BLOB columns follow a BLOB column in this case, those columns will be inaccessible.

BLOBs can be accessed only from the most recent row. Users must access the BLOB data columns, as well as all other columns, in the ascending column order; data in any columns skipped in the process of traversing the row will be lost. There is no change to the behavior of the columns preceding the first BLOB column; these columns are buffered in the usual manner.

Data Type Conversion

The ODBC Provider supports limited conversions. Only the default types returned in IColumnsInfo are supported, and DBTYPE_BYREF is supported only on variable length types. Also, some types returned by IColumnsInfo currently cannot be converted to or from DBTYPE_WSTR, which should be valid for all types.

Note  Supported data conversions can be determined through the IConvertType interface.

In the following table, click on the data type you want to convert from to view the supported conversions. In the conversion tables, an X indicates that the conversion is supported in the ODBC Provider.

Data Types to Convert From

I1	R4	DBTIMESTAMP	IUNKNOWN
I2	R8	BOOL	NULL
I4	CY	BSTR	STR
I8	DECIMAL	BYTES	VARIANT
UI1	NUMERIC	Empty	WSTR
UI2	DATE	ERROR
UI4	DBDATE	GUID
UI8	DBTIME	IDISPATCH
Note  When converting DBTYPE_NUMERIC types that correspond to ODBC types SQL_NUMERIC and SQL_DECIMAL, the ODBC Provider exposes additional conversions that are not listed as supported. This occurs because these types are internally bound to SQL_CHAR. Therefore, any conversions supported for CHAR data types in ODBC can be attempted for DBTYPE_NUMERIC types. There are some conversions that will fail, due to incompatibility, and some that will succeed, depending on the data.


Query-Based Updates

This section describes query-based updates and explains how rows are identified through the WHERE clause. It also describes the conditions under which rowsets can be updated based on query-based updates.

Query-Based Update Definition

Query-based updates use the SQL INSERT, UPDATE and DELETE commands to issue updates to the ODBC database. These commands are not positioned and are independent of, but in the same transaction context as, the rowset cursor. The purpose of these notes is not to document the issues related to query-based updates. They only cover those aspects which are specific to the ODBC Provider’s implementation. Two specific issues will be addressed: how the rows to be updated are identified and when a rowset can be updated using query-based updates. Query-based updates are used when the ODBC driver does not support the features required to use positioned updates.

Row Identification

When updating or deleting an existing row, query-based update must identify the row through the WHERE clause of the UPDATE or DELETE command. Because it does not have any primary key information, a query-based update constructs a hopeful key that it uses to identify the row. The hopeful key consists of all searchable columns in the rowset. By default, all REAL, FLOAT, and DOUBLE numeric values are excluded from the WHERE clause; to include these values, users must set the property KAGPROP_INCLUDENONEXACT to VARIANT_TRUE. A column is considered searchable if the provider returns SQL_SEARCHABLE or SQL_ALL_EXCEPT_LIKE for the SQLColAttributes descriptor SQL_COLUMN_SEARCHABLE. In other words, the column is considered searchable if the provider supports equality tests on that column in the WHERE clause. It is called the hopeful key because the user is hopeful, but cannot be sure, that the columns in the hopeful key contain a key for the rowset. If the hopeful key is not a key then update anomalies are possible when using query-based updates.

For example if a rowset contains columns A, B, C, and D where columns A and D are searchable, the DELETE command that would be issued by a query-based update would be:

DELETE FROM table-name WHERE (A = value-a AND D = value-d)

Value-x is the value of column x for the row being deleted.

Row identification is not an issue for inserts because a new row is being created, an existing row is not being changed or deleted.

Criteria for Updatable Rowsets

To be updatable, using query-based updates, the rowset must satisfy two criteria. First, the hopeful key for the rowset must contain at least one column. A rowset that does not contain any searchable columns is not updatable. Second, the query used to create the rowset must contain only a single simple table or view name in its FROM clause. The definition of a single simple name is a string that contains only alphanumeric characters and periods (.). (A period is allowed to permit schema-name.table-view-name). More complex single names and multi-table queries are not supported.

Ensuring Changes are Visible

To ensure that changes are visible, the consumer should set DBPROP_OTHERUPDATEDELETE or DBPROP_OTHERINSERT. This enables the ODBC Provider to expose changes to the data source using query-based updates.


Known Limitations

The following is a list of items in which the ODBC Provider known to be out of compliance with the OLE DB specification. It also lists areas that have not been thoroughly tested.

General

•	The ODBC Provider does not return precision correctly for any numeric data types except DBTYPE_NUMERIC and DBTYPE_DECIMAL. For example, the COLUMNS schema rowset, IColumnsInfo::GetColumnInfo, and IColumnsRowset::GetColumnsRowset return a precision of zero for integer data types.

•	The ODBC Desktop Database Driver cannot start a transaction when it has a rowset open. The ODBC Provider returns E_FAIL if ITransactionLocal::StartTransaction is called when any rowset is held open by this driver. This limitation does not necessarily affect other ODBC drivers.

•	The ODBC Provider supports a limit of one data source object per consumer application.

•	Query-based updates cannot be used to update BLOBs or other long data.

•	If DBMEMOWNER_PROVIDEROWNED was set in dwMemOwner in a binding structure and the binding structure binds to a long data column, the ODBC Provider returns DB_E_BADBINDINFO.

•	If DBPROP_REMOVEDELETED is set to VARIANT_TRUE on the rowset and a dynamic ODBC cursor is requested and the ODBC Provider cannot provide this type of cursor, any deleted rows scheduled to be removed from the rowset are removed at fetch time. In this situation, it is possible for row-fetching methods (such as GetNextRows or GetRowsAt) to return both S_OK and fewer rows than requested. This irregular behavior is caused by the ODBC Provider requirement to maintain a one-to-one correspondence between OLE DB and ODBC rowsets. Note that this behavior does not signify the DB_S_END_OF_ROWSET condition and that the number of rows returned by the fetching method will never be zero if there are any remaining rows (although the number of rows fetched may be smaller than the number requested).

•	The ODBC Provider cannot return bookmarks on newly-inserted rows. The ODBC Provider marks the status of the bookmark column of the newly-inserted row as DBSTATUS_E_UNAVAILABLE.

•	The ODBC Provider does not allow BLOB data to be set on newly-inserted rows. This is because newly-inserted rows can be updated only by query-based updates and query-based updates do not work with BLOB columns.

Microsoft SQL Server ODBC Driver

•	Using a Microsoft SQL Server database as the data source, if the consumer specifies a long column in the WHERE clause of a SELECT statement, the long column cannot contain more than 255 characters.

•	Using a Microsoft SQL Server database as the data source, if the consumer uses SetData to update a BLOB column through an ISequentialStream interface, the length part of the binding must contain the total length of the BLOB data. This differs from other data sources, which require the length part of the binding to contain only the length of the pointer to the ISequentialStream object.

•	Using a Microsoft SQL Server database as the data source, if the consumer opens a forward-only cursor using a query-based update, any method of the IRowsetChange interface may cause a deadlock situation.

The following schema and schema restrictions are supported through the Microsoft SQL Server ODBC driver.

Supported Schema	Supported Restrictions
CATALOGS	None
COLUMNS	All
TABLES	All
PROVIDER_TYPES	None
INDEXES	1, 2, and 5
PROCEDURE_PARAMETERS	All
COLUMN_PRIVILEGES	1, 2, 3, and 4
FOREIGN_KEYS	All
PRIMARY_KEYS	All
SCHEMATA	None
Microsoft Access ODBC Driver

•	When a consumer specifies a value to be inserted or updated in a command that causes a data overflow, but has not called ICommandWithParameters::SetParameterInfo, and the provider cannot determine the column type used by the data source, the ODBC Provider returns DB_E_DATAOVERFLOW (rather than DB_E_ERRORSOCCURRED with the offending parameter marked with the DBSTATUS_E_DATAOVERFLOW flag). This occurs because the ODBC Provider cannot detect which parameter caused the overflow error.

•	In delayed-update mode, consumers that attempt to delete or update rows in a read-only rowset receive the status DBROWSTATUS_DELETED. This differs from the expected status, which is DBROWSTATUS_INTEGRITYVIOLATION.

•	The rowset property DBPROP_MAXROWS does not affect the number of rows returned by the Microsoft Access ODBC driver.

The following schema and schema restrictions are supported through the Microsoft Access ODBC driver.

Supported Schema	Supported Restrictions
CATALOGS	None
COLUMNS	All
TABLES	All
PROVIDER_TYPES	None
INDEXES	1, 2, and 5
PROCEDURE_PARAMETERS	All
PROCEDURE_COLUMNS	All
Notifications

•	On the first change to a row from SetData, the ODBC Provider does not interleave the SynchAfter and DidEvent notifications. The ordering of OKTODO and ABOUTTODO is correct.

Rowset Properties

•	The rowset properties DBPROP_OTHERINSERT and DBPROP_CANHOLDROWS cannot be used together. If you attempt to create a rowset with this combination of properties, the ODBC Provider returns DB_E_ERRORSOCCURRED and returns _NOTSUPPORTED for both properties.

Setting Read-Only Properties to their Default Values

•	The ODBC Provider does not support the OLE DB version 1.1 behavior of allowing consumers to set the value of a read-only property to its default. Although the OLE DB version 1.1 specification clearly indicates that a status of DBPROPSTATUS_OK is returned in “the case where the value of a read-only property was set to its current value,” the ODBC Provider returns DBPROPSTATUS_NOTSETTABLE.

Threading

•	The ODBC Provider supports the Free Threaded multithreading model; there is no support for Apartment model or mixed threading.

ICommandWithParameters

•	The ODBC Provider returns zero for precision and scale when these values do not apply. These values are incorrect and should be ~0 (bitwise NOT 0).

IConvertType

•	IConvertType::CanConvert returns E_INVALIDARG when a bad dwConvert flag is passed in. This is incorrect; the ODBC Provider should return DB_E_BADCONVERTFLAG.

IDBSchemaRowset

•	The ODBC Provider does not support restrictions on the CATALOGS and SCHEMATA schema rowsets.

•	IDBSchemaRowset::GetSchema does not support the schema restriction on any schema when the underlying ODBC driver is the Microsoft Access driver. The ODBC Provider will incorrectly report this as a supported restriction because ODBC drivers cannot export information about their support of restrictions to the ODBC Provider.

•	IDBSchemaRowset::GetRowset features limited support for the following schema rowsets
FOREIGN_KEYS
PRIMARY_KEYS
STATISTICS
with most data sources. Consumers must restrict their calls by table name or the ODBC Provider will return an empty result set. This limitation does not occur on Microsoft SQL Server 4.21.

•	On Microsoft SQL Server data sources, IDBSchemaRowset::GetRowset( DBSCHEMA_PROCEDURE_COLUMNS) is unsupported. Consumers that need rowset information about stored procedures should prepare a query that includes the stored procedure and call IColumnsInfo::GetColumnInfo.

•	A restriction on table name is required when calling IDBSchemaRowset::GetRowset(DBSCHEMA_INDEXES). This restriction must be used because ODBC function SQLStatistics returns information about a single table and the ODBC Provider uses this function to return information from the data source. If the restriction is not applied, the ODBC Provider returns an empty result set.

•	The PROVIDER_TYPES schema rowset is not sorted by the ODBC Provider.

IRowset

•	The ODBC Provider does not support execution of RestartPosition when the command text contains parameters; the underlying ODBC driver must, however, support SQLExtendedFetch..

•	If the columns in the underlying data source change and the consumer requests additional rows with IRowset::GetNextRows, the ODBC Provider will fail with the return code E_FAIL.

•	If the columns in the underlying data source change, the consumer calls IRowset::RestartPosition, and the reposition operation causes the command to be re-executed, the ODBC Provider will fail with the return code E_FAIL. The rowset is now invalid and any method other than ReleaseRows or ReleaseAccessor will fail. If this occurs, the consumer must release all accessors, row handles, and the rowset and execute the command that created the rowset again.

•	When using IRowset::GetData, you can bind to only one long data column based on ISequentialStream; the ODBC Provider can bind to any number of long data columns not based on ISequentialStream.

IRowsetLocate

•	When the consumer fetches a row with IRowsetLocate::GetRowsAt, saves the bookmark value corresponding to the row, releases the row, and attempts to fetch the row again using IRowsetLocate::GetRowsAt with the saved bookmark, the second fetch operation may fail. The ODBC Provider returns DB_E_BADBOOKMARK if the underlying ODBC call to SQLExtendedFetch returns S1111 (invalid bookmark value); otherwise, the ODBC Provider returns E_FAIL.

IRowsetResynch

•	To enable the ODBC Provider to expose the IRowsetResynch interface, consumers must meet at least one of the following conditions:

•	The IRowsetLocate interface is implemented.

•	The transaction isolation level is set to SQL_TXN_REPEATABLE_READ.

•	The transaction isolation level is set to SQL_TXN_SERIALIZABLE.

•	The transaction isolation level is set to SQL_TXN_VERSIONING.

•	DBPROP_CANHOLDROWS is set to VARIANT_FALSE, SQLExtendedFetch and SQLSetPos with REFRESH are available from the underlying ODBC driver, and the cursor is not a forward-only cursor.


Conversions Supported From Data Type I1

Convert to Type			Convert to Type
I2			DBTIME
I4			DBTIMESTAMP
I8			BOOL	X
UI1	X		BSTR	X
UI2			BYTES
UI4			Empty	X
UI8			ERROR
R4			GUID
R8			IDISPATCH
CY			IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT
DBDATE			WSTR	X


Conversions Supported From Data Type I2

Convert to Type			Convert to Type
I1			DBTIME
I4	X		DBTIMESTAMP
I8			BOOL	X
UI1			BSTR	X
UI2	X		BYTES
UI4	X		Empty	X
UI8			ERROR
R4	X		GUID
R8	X		IDISPATCH
CY	X		IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT	X
DBDATE			WSTR	X


Conversions Supported From Data Type I4

Convert to Type			Convert to Type
I1			DBTIME
I2	X		DBTIMESTAMP
I8			BOOL	X
UI1			BSTR	X
UI2			BYTES
UI4	X		Empty	X
UI8			ERROR
R4	X		GUID
R8	X		IDISPATCH
CY	X		IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT	X
DBDATE			WSTR	X


Conversions Supported From Data Type I8

No supported conversions.


Conversions Supported From Data Type UI1

Convert to Type			Convert to Type
I1	X		DBTIME
I2			DBTIMESTAMP
I4			BOOL	X
I8			BSTR	X
UI2			BYTES
UI4			Empty	X
UI8			ERROR
R4			GUID
R8			IDISPATCH
CY			IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT
DBDATE			WSTR	X


Conversions Supported From Data Type UI2

Convert to Type			Convert to Type
I1			DBTIME
I2	X		DBTIMESTAMP
I4	X		BOOL	X
I8			BSTR	X
UI1			BYTES
UI4	X		Empty	X
UI8			ERROR
R4	X		GUID
R8	X		IDISPATCH
CY			IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT
DBDATE			WSTR	X


Conversions Supported From Data Type UI4

Convert to Type			Convert to Type
I1			DBTIME
I2			DBTIMESTAMP
I4	X		BOOL	X
I8			BSTR	X
UI1			BYTES	X
UI2			Empty	X
UI8			ERROR
R4	X		GUID
R8	X		IDISPATCH
CY			IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT
DBDATE			WSTR	X


Conversions Supported From Data Type UI8

No supported conversions.


Conversions Supported From Data Type R4

Convert to Type			Convert to Type
I1			DBTIME
I2	X		DBTIMESTAMP
I4	X		BOOL	X
I8			BSTR	X
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R8	X		IDISPATCH
CY	X		IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT	X
DBDATE			WSTR	X


Conversions Supported From Data Type R8

Convert to Type			Convert to Type
I1			DBTIME
I2	X		DBTIMESTAMP
I4	X		BOOL	X
I8			BSTR	X
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R4	X		IDISPATCH
CY	X		IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT	X
DBDATE			WSTR	X


Conversions Supported From Data Type CY

Convert to Type			Convert to Type
I1			DBTIME
I2	X		DBTIMESTAMP
I4	X		BOOL	X
I8			BSTR	X
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R4	X		IDISPATCH
R8	X		IUNKNOWN
DECIMAL			NULL	X
NUMERIC			STR	X
DATE	X		VARIANT	X
DBDATE			WSTR	X


Conversions Supported From Data Type DECIMAL

Convert to Type			Convert to Type
I1			DBTIME
I2			DBTIMESTAMP
I4			BOOL
I8			BSTR
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R4			IDISPATCH
R8			IUNKNOWN
CY			NULL	X
NUMERIC			STR
DATE			VARIANT
DBDATE			WSTR


Conversions Supported From Data Type NUMERIC

Convert to Type			Convert to Type
I1			DBTIME
I2			DBTIMESTAMP
I4			BOOL
I8			BSTR	X
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R4			IDISPATCH
R8			IUNKNOWN
CY			NULL	X
DECIMAL			STR	X
DATE			VARIANT
DBDATE			WSTR	X


Conversions Supported From Data Type DATE

Convert to Type			Convert to Type
I1			DBTIME
I2	X		DBTIMESTAMP
I4	X		BOOL	X
I8			BSTR	X
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R4	X		IDISPATCH
R8	X		IUNKNOWN
CY	X		NULL	X
DECIMAL			STR	X
NUMERIC			VARIANT	X
DBDATE			WSTR	X


Conversions Supported From Data Type DBDATE

Convert to Type			Convert to Type
I1			DBTIME
I2			DBTIMESTAMP
I4			BOOL
I8			BSTR	X
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R4			IDISPATCH
R8			IUNKNOWN
CY			NULL	X
DECIMAL			STR	X
NUMERIC			VARIANT
DATE			WSTR	X


Conversions Supported From Data Type DBTIME

Convert to Type			Convert to Type
I1			DBDATE
I2			DBTIMESTAMP
I4			BOOL
I8			BSTR	X
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R4			IDISPATCH
R8			IUNKNOWN
CY			NULL	X
DECIMAL			STR	X
NUMERIC			VARIANT
DATE			WSTR	X


Conversions Supported From Data Type DBTIMESTAMP

Convert to Type			Convert to Type
I1			DBDATE
I2			DBTIME
I4			BOOL
I8			BSTR	X
UI1			BYTES
UI2			Empty	X
UI4			ERROR
UI8			GUID
R4			IDISPATCH
R8			IUNKNOWN
CY			NULL	X
DECIMAL			STR	X
NUMERIC			VARIANT
DATE			WSTR	X


Conversions Supported From Data Type BOOL

Convert to Type			Convert to Type
I1			DBDATE
I2	X		DBTIME
I4	X		DBTIMESTAMP
I8			BSTR	X
UI1			BYTES
UI2	X		Empty	X
UI4	X		ERROR
UI8			GUID
R4	X		IDISPATCH
R8	X		IUNKNOWN
CY	X		NULL	X
DECIMAL			STR	X
NUMERIC			VARIANT	X
DATE	X		WSTR	X


Conversions Supported From Data Type BSTR

Convert to Type			Convert to Type
I1	X		DBDATE	X
I2	X		DBTIME	X
I4	X		DBTIMESTAMP	X
I8			BOOL	X
UI1	X		BYTES	X
UI2	X		Empty	X
UI4	X		ERROR
UI8			GUID	X
R4	X		IDISPATCH
R8	X		IUNKNOWN
CY	X		NULL	X
DECIMAL			STR	X
NUMERIC	X		VARIANT	X
DATE	X		WSTR	X


Conversions Supported From Data Type BYTES

Convert to Type			Convert to Type
I1			DBDATE
I2			DBTIME
I4			DBTIMESTAMP
I8			BOOL
UI1			BSTR	X
UI2			Empty	X
UI4	X		ERROR
UI8			GUID
R4			IDISPATCH
R8			IUNKNOWN
CY			NULL	X
DECIMAL			STR	X
NUMERIC			VARIANT
DATE			WSTR	X


Conversions Supported From Data Type Empty

Convert to Type			Convert to Type
I1	X		DBDATE
I2	X		DBTIME
I4	X		DBTIMESTAMP
I8			BOOL	X
UI1	X		BSTR	X
UI2	X		BYTES
UI4	X		ERROR
UI8			GUID
R4	X		IDISPATCH
R8	X		IUNKNOWN
CY	X		NULL	X
DECIMAL			STR	X
NUMERIC			VARIANT	X
DATE	X		WSTR	X


Conversions Supported From Data Type ERROR

Convert to Type			Convert to Type
I1			DBDATE
I2			DBTIME
I4			DBTIMESTAMP
I8			BOOL
UI1			BSTR
UI2			BYTES
UI4			Empty
UI8			GUID
R4			IDISPATCH
R8			IUNKNOWN
CY			NULL
DECIMAL			STR
NUMERIC			VARIANT	X
DATE			WSTR


Conversions Supported From Data Type GUID

Convert to Type			Convert to Type
I1			DBDATE
I2			DBTIME
I4			DBTIMESTAMP
I8			BOOL
UI1			BSTR	X
UI2			BYTES
UI4			Empty	X
UI8			ERROR
R4			IDISPATCH
R8			IUNKNOWN
CY			NULL	X
DECIMAL			STR	X
NUMERIC			VARIANT
DATE			WSTR	X


Conversions Supported From Data Type IDISPATCH

Convert to Type			Convert to Type
I1			DBDATE
I2			DBTIME
I4			DBTIMESTAMP
I8			BOOL
UI1			BSTR
UI2			BYTES
UI4			Empty	X
UI8			ERROR
R4			GUID
R8			IUNKNOWN	X
CY			NULL	X
DECIMAL			STR
NUMERIC			VARIANT	X
DATE			WSTR


Conversions Supported From Data Type IUNKNOWN

Convert to Type			Convert to Type
I1			DBDATE
I2			DBTIME
I4			DBTIMESTAMP
I8			BOOL
UI1			BSTR
UI2			BYTES
UI4			Empty	X
UI8			ERROR
R4			GUID
R8			IDISPATCH	X
CY			NULL	X
DECIMAL			STR
NUMERIC			VARIANT	X
DATE			WSTR


Conversions Supported From Data Type NULL

Convert to Type			Convert to Type
I1			DBDATE
I2			DBTIME
I4			DBTIMESTAMP
I8			BOOL
UI1			BSTR
UI2			BYTES
UI4			Empty
UI8			ERROR
R4			GUID
R8			IDISPATCH
CY			IUNKNOWN
DECIMAL			STR
NUMERIC			VARIANT	X
DATE			WSTR


Conversions Supported From Data Type STR

Convert to Type			Convert to Type
I1	X		DBDATE	X
I2	X		DBTIME	X
I4	X		DBTIMESTAMP	X
I8			BOOL	X
UI1	X		BSTR	X
UI2	X		BYTES	X
UI4	X		Empty	X
UI8			ERROR
R4	X		GUID	X
R8	X		IDISPATCH
CY	X		IUNKNOWN
DECIMAL			NULL	X
NUMERIC	X		VARIANT	X
DATE	X		WSTR	X


Conversions Supported From Data Type VARIANT

Convert to Type			Convert to Type
I1	X		DBDATE
I2	X		DBTIME
I4	X		DBTIMESTAMP
I8			BOOL	X
UI1	X		BSTR	X
UI2	X		BYTES
UI4	X		Empty	X
UI8			ERROR	X
R4	X		GUID
R8	X		IDISPATCH	X
CY	X		IUNKNOWN	X
DECIMAL	X		NULL	X
NUMERIC			STR	X
DATE	X		WSTR	X


Conversions Supported From Data Type WSTR

Convert to Type			Convert to Type
I1	X		DBDATE	X
I2	X		DBTIME	X
I4	X		DBTIMESTAMP	X
I8			BOOL	X
UI1	X		BSTR	X
UI2	X		BYTES
UI4	X		Empty	X
UI8			ERROR
R4	X		GUID	X
R8	X		IDISPATCH
CY	X		IUNKNOWN
DECIMAL			NULL	X
NUMERIC	X		STR	X
DATE	X		VARIANT	X